package sqldriver

import (
	"database/sql"
	"fmt"
	"gitee.com/hakwolf/dbmagic/golib/internal/modules/configdbg"
	_ "github.com/mattn/go-adodb"
	"strconv"
	"strings"
)

type Mssql struct {
	*sql.DB
	DataSource string
	Database   string
	Windows    bool
	Sa         SA
}

type SA struct {
	User   string
	Passwd string
}

func (m *Mssql) Open() (err error) {
	var conf []string
	conf = append(conf, "Provider=SQLOLEDB")
	conf = append(conf, "Data Source="+m.DataSource)
	if m.Windows {
		// Integrated Security=SSPI 这个表示以当前WINDOWS系统用户身去登录SQL SERVER服务器(需要在安装sqlserver时候设置)，
		//如果SQL SERVER服务器不支持这种方式登录时，就会出错。
		conf = append(conf, "integrated security=SSPI")
	}
	conf = append(conf, "Initial Catalog="+m.Database)
	conf = append(conf, "user id="+m.Sa.User)
	conf = append(conf, "password="+m.Sa.Passwd)

	m.DB, err = sql.Open("adodb", strings.Join(conf, ";"))
	if err != nil {
		return err
	}
	return nil
}

func (m *Mssql) GetDsn() (ret string) {
	var conf []string
	conf = append(conf, "Provider=SQLOLEDB")
	conf = append(conf, "Data Source="+m.DataSource)
	if m.Windows {
		// Integrated Security=SSPI 这个表示以当前WINDOWS系统用户身去登录SQL SERVER服务器(需要在安装sqlserver时候设置)，
		// 如果SQL SERVER服务器不支持这种方式登录时，就会出错。
		conf = append(conf, "integrated security=SSPI")
	}
	conf = append(conf, "Initial Catalog="+m.Database)
	conf = append(conf, "user id="+m.Sa.User)
	conf = append(conf, "password="+m.Sa.Passwd)

	return strings.Join(conf, ";")

}

func GetMssqlDbTest(host, user, psd, dbname string, bMode bool, port int64) Mssql {
	var dataSource string
	if port != 0 {
		strInt64 := strconv.FormatInt(port, 10)
		id16, _ := strconv.Atoi(strInt64)
		dataSource = host + "," + strconv.Itoa(id16) //setting.Db.Host
	} else {
		dataSource = host
	}
	return Mssql{
		DataSource: dataSource,
		Database:   dbname,
		// windwos: true 为windows身份验证，false 必须设置sa账号和密码
		Windows: bMode,
		Sa: SA{
			User:   user,
			Passwd: psd,
		},
	}
}

func main() {
	db := Mssql{
		DataSource: "TVPN\\SQL2017TEST",
		Database:   "master",
		// windwos: true 为windows身份验证，false 必须设置sa账号和密码
		Windows: false,
		Sa: SA{
			User:   "sa",
			Passwd: "sql",
		},
	}
	// 连接数据库
	err := db.Open()
	if err != nil {
		configdbg.Println("sql open:", err)
		return
	}
	defer db.Close()

	// 执行SQL语句
	rows, err := db.Query("SELECT name FROM sys.databases")
	if err != nil {
		configdbg.Println("query: ", err)
		return
	}
	for rows.Next() {
		var name string
		//var number int
		rows.Scan(&name)                   //, &number
		fmt.Printf("Name: %s \t \n", name) //Number: %d  , number
	}
}

//func EXECadodbsql(req *utils.DbTaskRequest,sql string)(err error){
//var dataSource string
//if utils.Port != 0 {
//	strInt64 := strconv.FormatInt(utils.Port, 10)
//	id16 ,_ := strconv.Atoi(strInt64)
//	dataSource = utils.Dip + "," + strconv.Itoa(id16) //setting.Db.Host
//} else {
//	dataSource = utils.Dip
//}
//db := Mssql{
//	DataSource: dataSource,
//	Database:   utils.Dbname,
//	// windwos: true 为windows身份验证，false 必须设置sa账号和密码
//	Windows: false,
//	Sa: SA{
//		User:   utils.Ac,
//		Passwd: utils.Pd,
//	},
//}
// 连接数据库
//ers := db.Open()
//if ers != nil {
//configdbg.Println("sql open:", err)
//return ers
//}
//defer db.Close()
//
//// 执行SQL语句
//    _, ers = db.Exec(sql)
//	if err != nil {
//		configdbg.Println("query: ", err)
//		return ers
//	}
//  return ers
//}

func EXECadodbsqlNew(db Mssql, sql, dbname string) (err error) {
	//var dataSource string
	//if hostinfo.Port != 0 {
	//	strInt64 := strconv.FormatInt(hostinfo.Port, 10)
	//	id16, _ := strconv.Atoi(strInt64)
	//	dataSource = hostinfo.Host + "," + strconv.Itoa(id16) //setting.Db.Host
	//} else {
	//	dataSource = hostinfo.Host
	//}
	//db := Mssql{
	//	DataSource: dataSource,
	//	Database:  dbname,
	//	// windwos: true 为windows身份验证，false 必须设置sa账号和密码
	//	Windows: false,
	//	Sa: SA{
	//		User:   hostinfo.User,
	//		Passwd: hostinfo.Password,
	//	},
	//}
	// 连接数据库

	ers := db.Open()
	if ers != nil {
		configdbg.Println("sql open:", err)
		return ers
	}
	defer db.Close()

	// 执行SQL语句
	_, ers = db.Exec(sql)
	if err != nil {
		configdbg.Println("query: ", err)
		return ers
	}
	return ers
}

//func EXECadodbsql2(req *pb.DbTaskRequest, sql string) (err error) {
//	var dataSource string
//	if req.Port != 0 {
//		strInt64 := strconv.FormatInt(req.Port, 10)
//		id16, _ := strconv.Atoi(strInt64)
//		dataSource = req.Dip + "," + strconv.Itoa(id16) //setting.Db.Host
//	} else {
//		dataSource = req.Dip
//	}
//	db := Mssql{
//		DataSource: dataSource,
//		Database:   req.Dbname,
//		// windwos: true 为windows身份验证，false 必须设置sa账号和密码
//		Windows: false,
//		Sa: SA{
//			User:   req.Ac,
//			Passwd: req.Pd,
//		},
//	}
//	// 连接数据库
//	ers := db.Open()
//	if ers != nil {
//		configdbg.Println("sql open:", err)
//		return ers
//	}
//	defer db.Close()
//
//	// 执行SQL语句
//	_, ers = db.Exec(sql)
//	if err != nil {
//		configdbg.Println("query: ", err)
//		return ers
//	}
//	return ers
//}
